﻿open LinqToTwitter
open LinqToTwitter.Security
open log4net
open System.Configuration
open System.IO
open System.Linq
open System.Data
open System.Data.SqlClient

let sMachineName = System.Net.Dns.GetHostName()
let sqlConn = ConfigurationManager.ConnectionStrings.["SqlServerTarget"].ToString()
let connDB = new SqlConnection(sqlConn)
let connDB1 = new SqlConnection(sqlConn)

[<EntryPoint>]
let main argv = 

    let program = System.Reflection.MethodBase.GetCurrentMethod().DeclaringType
    let logger = log4net.LogManager.GetLogger(program)
    let cfgFile = FileInfo("Log4Net.config")
    log4net.Config.XmlConfigurator.Configure(cfgFile) |> ignore
    logger.Info(program.FullName + " starting on " + sMachineName + ".")
    
    let cs =
        { new SingleUserInMemoryCredentialStore() with
                member this.ConsumerKey = ConfigurationManager.AppSettings.Get("APIkey")
                member this.ConsumerSecret = ConfigurationManager.AppSettings.Get("APIsecret")
                member this.AccessToken = ConfigurationManager.AppSettings.Get("accessToken")
                member this.AccessTokenSecret = ConfigurationManager.AppSettings.Get("accessTokenSecret")
        }

    let mutable auth = new SingleUserAuthorizer() 
    auth.CredentialStore <- cs
    let twitterCtx = new TwitterContext(auth)
 
    connDB.Open()


    let sbc = new SqlBulkCopy(connDB, DestinationTableName="[Tweet]")
    let sbcHT = new SqlBulkCopy(connDB, DestinationTableName="[Hashtag_Tweet]")
    let sbcURL = new SqlBulkCopy(connDB, DestinationTableName="[Tweet_URL]")
    let sbcUM = new SqlBulkCopy(connDB, DestinationTableName="[Tweet_UserMention]")
    let sbcUser = new SqlBulkCopy(connDB, DestinationTableName="[Tweet_User]")

    let dt = new DataTable()
    ["StatusID", typeof<System.Int64>
     "UserID", typeof<System.Int64>
     "PossiblySensitive", typeof<bool>
     "RetweetCount", typeof<System.Int32>
     "Retweeted", typeof<bool>
     "Text", typeof<string>
     "CreatedAt", typeof<System.DateTime>
     "ScreenNameResponse", typeof<string>
     "UserIDResponse", typeof<string>]
    |> List.iter (dt.Columns.Add>>ignore)

    let dtHT = new DataTable()
    ["Hashtag", typeof<string>
     "StatusID", typeof<System.Int64>]
    |> List.iter (dtHT.Columns.Add>>ignore)

    let dtURL = new DataTable()
    ["StatusID", typeof<System.Int64>
     "DisplayURL", typeof<string>
     "ExpandedURL", typeof<string>
     "URL", typeof<string>]
    |> List.iter (dtURL.Columns.Add>>ignore)

    let dtUM = new DataTable()
    ["UserID", typeof<System.Int64>
     "StatusID", typeof<System.Int64>]
    |> List.iter (dtUM.Columns.Add>>ignore)

    let dtUser = new DataTable()
    ["StatusID", typeof<System.Int64>
     "UserID", typeof<System.Int64>
     "CreatedAt", typeof<System.DateTime>
     "Description", typeof<string>
     "FavoritesCount", typeof<System.Int32>
     "FollowersCount", typeof<System.Int32>
     "FriendsCount", typeof<System.Int32>
     "GeoEnabled", typeof<bool>
     "IncludeEntities", typeof<bool>
     "IsTranslator", typeof<bool>
     "Lang", typeof<string>
     "LangResponse", typeof<string>
     "ListedCount", typeof<System.Int32>
     "Location", typeof<string>
     "Name", typeof<string>
     "Notifications", typeof<bool>
     "Protected", typeof<bool>
     "ScreenName", typeof<string>
     "ScreenNameList", typeof<string>
     "ScreenNameResponse", typeof<string>
     "StatusesCount", typeof<System.Int32>
     "Url", typeof<string>
     "UserIDResponse", typeof<string>
     "Verified", typeof<bool>]    
    |> List.iter (dtUser.Columns.Add>>ignore)

    connDB1.Open()
    
    let cmd = new SqlCommand("Select SearchTerm from [Twitter].[dbo].[SearchTerms] where Active = 1 order by SearchTerm", connDB1)
    use reader = cmd.ExecuteReader()

    while reader.Read() do
        let st = unbox(reader.["SearchTerm"])
        printfn "searching for: %s" st
        let mutable sinceID1 = 1UL
        let mutable maxID1 =  9223372036854775807UL
        let mutable continueLooping = true

        while continueLooping do
            let sinceID = sinceID1
            let maxID = maxID1
            let queryResults = query {
                for search in twitterCtx.Search do
                where (search.Type = SearchType.Search && search.Count = 100 && search.SinceID = sinceID && search.MaxID = maxID)
                where (search.Query = st)
            
                select search 
                }
        //        |> Seq.toList

            if (twitterCtx.RateLimitRemaining > 0 && twitterCtx.RateLimitRemaining < 25) then
                printfn "%i - %i \n" twitterCtx.RateLimitCurrent twitterCtx.RateLimitRemaining
                System.Threading.Thread.Sleep(60000 * 20/twitterCtx.RateLimitRemaining)
//            for s in twitterCtx.Status do
//                printfn "-- &s" s.
            
            if (queryResults = null) then
                printfn "error."
            else
                let r = queryResults.Single()

                for twt in r.Statuses do
                    let dr = dt.NewRow()
                    dr.["StatusID"] <- twt.StatusID
                    dr.["UserID"] <- twt.UserID
                    dr.["PossiblySensitive"] <- twt.PossiblySensitive
                    dr.["RetweetCount"] <- twt.RetweetCount
                    dr.["Retweeted"] <- twt.Retweeted
                    dr.["Text"] <- twt.Text
                    dr.["CreatedAt"] <- twt.CreatedAt
                    dr.["ScreenNameResponse"] <- twt.User.ScreenNameResponse
                    dr.["UserIDResponse"] <- twt.User.UserIDResponse
                    let entities = twt.Entities
                    for ht in entities.HashTagEntities do
                        let drHT = dtHT.NewRow()
                        drHT.["Hashtag"] <- ht.Tag
                        drHT.["StatusID"] <- twt.StatusID
                        dtHT.Rows.Add(drHT)
                    for ue in entities.UrlEntities do
                        let drURL = dtURL.NewRow()
                        drURL.["StatusID"] <- twt.StatusID
                        drURL.["DisplayURL"] <- ue.DisplayUrl
                        drURL.["ExpandedURL"] <- ue.ExpandedUrl
                        drURL.["URL"] <- ue.Url
                        dtURL.Rows.Add(drURL)
                    for ume in entities.UserMentionEntities do
                        let drUM = dtUM.NewRow()
                        drUM.["StatusID"] <- twt.StatusID
                        drUM.["UserID"] <- ume.Id
                        dtUM.Rows.Add(drUM)
                    dt.Rows.Add(dr)
                    let user = twt.User
                    let drU = dtUser.NewRow()
                    drU.["StatusID"] <- twt.StatusID
                    drU.["UserID"] <- user.UserID
                    drU.["CreatedAt"] <- user.CreatedAt
                    drU.["Description"] <- user.Description
                    drU.["FavoritesCount"] <- user.FavoritesCount
                    drU.["FollowersCount"] <- user.FollowersCount
                    drU.["FriendsCount"] <- user.FriendsCount
                    drU.["GeoEnabled"] <- user.GeoEnabled
                    drU.["IncludeEntities"] <- user.IncludeEntities
                    drU.["IsTranslator"] <- user.IsTranslator
                    drU.["Lang"] <- user.IsTranslator
                    drU.["LangResponse"] <- user.LangResponse
                    drU.["ListedCount"] <- user.ListedCount
                    drU.["Location"] <- user.Location
                    drU.["Name"] <- user.Name
                    drU.["Notifications"] <- user.Notifications
                    drU.["Protected"] <- user.Protected
                    drU.["ScreenName"] <- user.ScreenName
                    drU.["ScreenNameList"] <- user.ScreenNameList
                    drU.["ScreenNameResponse"] <- user.ScreenNameResponse
                    drU.["StatusesCount"] <- user.StatusesCount
                    drU.["Url"] <- user.Url
                    drU.["UserIDResponse"] <- user.UserIDResponse
                    drU.["Verified"] <- user.Verified
                    dtUser.Rows.Add(drU)
                    maxID1 <- twt.StatusID 
            
            if (maxID = maxID1) then
                continueLooping <- false
            maxID1 <- maxID1 - 1UL

    sbc.WriteToServer(dt)
    sbcHT.WriteToServer(dtHT)   
    sbcURL.WriteToServer(dtURL) 
    sbcUM.WriteToServer(dtUM) 
    sbcUser.WriteToServer(dtUser)
     
    connDB.Close()
    logger.Info(program.FullName + " exiting on " + sMachineName + ".")
    0 // return an integer exit code

